package fresher.model;

import fresher.objects.Bill;
import fresher.utility.DatabaseUtility;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 *
 * Bill business model
 */
public class BillModel {

    public ArrayList<Bill> getAll() {
        DatabaseUtility database = new DatabaseUtility();
        String sql = "SELECT * FROM bill";
        ArrayList<Bill> list = new ArrayList<Bill>();
        try {
            ResultSet result = database.query(sql);
            while (result.next()) {
                Bill bill = new Bill(result.getInt(1), result.getInt(2), result.getInt(3), result.getInt(4));
                list.add(bill);
            }

        } catch (SQLException sqlException) {
            database.close();
            return list;
        }
        database.close();
        return list;

    }

    public void insert(int tableId, long date, int price) {
        DatabaseUtility database = new DatabaseUtility();
        String sql = "INSERT INTO bill(`table_id`,`date`,`total`)"
                + "VALUES (" + tableId + ", " + date + ", " + price + ")";
        try {
            database.executeUpdate(sql);
        } catch (SQLException sqlException) {
            database.close();
        }
        database.close();
    }

    public Bill getBill(int id) {
        DatabaseUtility database = new DatabaseUtility();
        String sql = "SELECT * FROM bill WHERE id = " + id;
        Bill bill = null;
        try {
            ResultSet result = database.query(sql);
            if (result.next()) {
                bill = new Bill(result.getInt(1), result.getInt(2), result.getInt(3), result.getInt(4));
            }
        } catch (SQLException sqlException) {
            database.close();
        }
        database.close();
        return bill;
    }

    public void update(int id, int tableId, long date, int price) {
        DatabaseUtility database = new DatabaseUtility();
        String sql = "UPDATE bill SET table_id= " + tableId + ", date = " + date + ", total = " + price + " WHERE "
                + "id = " + id;
        try {
            database.executeUpdate(sql);
        } catch (SQLException sqlException) {
            database.close();
        }
        database.close();
    }

    public void delete(int id) {
        DatabaseUtility database = new DatabaseUtility();
        String sql = "DELETE FROM bill WHERE id=" + id;

        try {
            database.executeUpdate(sql);
        } catch (SQLException sqlException) {
            database.close();
        }
        database.close();
    }
    
    public void updateTotalOfBill(int id, int price) {
        DatabaseUtility database = new DatabaseUtility();
        String sql = "UPDATE bill SET total = " + price + " WHERE "
                + "id = " + id;
        try {
            database.executeUpdate(sql);
        } catch (SQLException sqlException) {
            database.close();
        }
        database.close();
    }
}
